#!/usr/bin/env python
# encoding: utf-8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #     
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : Globallawonline                #
#                                                                   #
#                   @File Name    : Findrevedit.py                      #
#                                                                   #
#                   @Programmer   : 李建                            #
#                                                                   #  
#                   @Start Date   : 2021/1/28 15:56                 #
#                                                                   #
#                   @Last Update  : 2021/1/28 15:56                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes:该类用于找修订版法律                                                          #
#                                                                   #
#-------------------------------------------------------------------#
"""
# 这是mysql数据库的连接属性
import pymysql

CJSQLCON = {
    'HOST': '114.115.219.219',
    'PORT': 3306,
    'DB': 'globallegaldatabase',
    'USER': 'root',
    'PASSWD': 'xinanzhengfadaqxueapp03!',
    'CHARSET': 'utf8'
}
table = "`新加坡法律`"
COUNTRY = {"LAWCOUNTRYXJP": ["Acts", "Subsidiary Legislation"]}
WEBSITE = {"Acts":["Revised Editions of Acts"], "Subsidiary Legislation":["Revised Editions of Subsidiary Legislation"]}
FILEDS = {"LAWCOUNTRYXJP": "ID,LegalName,ChapNo,SYSID"}
urlconn = pymysql.connect(host=CJSQLCON['HOST'], port=CJSQLCON['PORT'], db=CJSQLCON['DB'],
                          user=CJSQLCON['USER'], passwd=CJSQLCON['PASSWD'], charset=CJSQLCON['CHARSET'])
cjurlcursor = urlconn.cursor()
revdict = {}
for country in list(COUNTRY.keys()):
    fileds = FILEDS[country]
    for website in COUNTRY[country]:
        for revedit_website in WEBSITE[website]:
            sql = '''select %s From %s WHERE Website = "%s"''' % (fileds,table,website)
            try:
                cjurlcursor.execute(sql)
                results = cjurlcursor.fetchall()
            except Exception as e:
                print("采集服务器查询出错:" + str(e))
                break
            sql = '''select %s From %s WHERE Website = "%s"''' % (fileds, table, revedit_website)
            try:
                cjurlcursor.execute(sql)
                resultls = cjurlcursor.fetchall()
            except Exception as e:
                print("采集服务器查询出错:" + str(e))
                break
            for resultl in resultls:
                for result in results:
                    Isrev = True
                    for i in range(1, len(resultl)-1):
                        if result[i] != resultl[i]:
                            Isrev = False
                            break
                    if Isrev:
                        if revdict.get(result[-1], 0):
                            revdict[result[-1]].append(resultl[-1])
                        else:
                            revdict.setdefault(result[-1], [resultl[-1]])
for sysid in list(revdict.keys()):
    revedits = ";".join(revdict[sysid])
    sql = '''UPDATE %s SET RevEdits = "%s" WHERE SYSID = "%s"''' % (table, revedits, sysid)
    try:
        cjurlcursor.execute(sql)
        urlconn.commit()
    except Exception as e:
        print("采集服务器更新出错:" + str(e))
cjurlcursor.close()
urlconn.close()
